package DCRS::Call;
#----------------------------------------------------------------------------
# Name:         Call.pm
# Purpose:      Process DCRS Clarion database files for call reports
# 
# Notes:        This module is part of the DCRS Dispatch Web Interface 
#
# Author:       Kingsley Wright
#
# Created:      2007-2008
# Copyright:    (c) 2008 by DCRS Solutions, Inc.
# Licence:      DBT - All rights reversed, DCRS Solutions, Inc.
#----------------------------------------------------------------------------
use strict;
use warnings;
use base 'CGI::Application';
use Clarion;
use Time::CTime;
use Tie::File::AsHash;
use DateTime::Format::Excel;
use Date::Calc qw(Today_and_Now Delta_YMDHMS Add_Delta_YMDHMS Delta_DHMS Date_to_Text);

# This is where we keep all of the .dat files.
my $DBPATH = '/media/officedb/PUBLIC/OFFICE4/';

# Use this object's parse_datetime method to format serial dates. 
# subtract 36161 from serial value for clarion serials.
my $dto = DateTime::Format::Excel->new(); 

# Globals 
my $today = [Today_and_Now()];
my $today_clarion = convert_date_to_clarion();
my $year_clarion = $today_clarion - 365;
my $localtime = localtime();
my $technames = get_tech_names();
my $customers = get_customer_list();
my $supcodes  = get_support_codes();

sub setup
{
    my $self = shift;
    $self->start_mode('search');
    $self->run_modes(
        search => 'call_search_form',
        report => 'call_report',
        activity => 'call_activity',
    );
}

sub call_search_form
{
    my $self     = shift;
    my $q        = $self->query();
    my $title    = "DCRS Customer Call Search Form";
    my $template = $self->load_tmpl(
        'call/search_form.tmpl',
        global_vars       => 1,
        loop_context_vars => 1,
        die_on_bad_params => 0
    ); 
 
    my %call_labels = (
        O => 'Open',
        C => 'Closed'
    );

    my %sort_labels = (
        NUMBER => 'Call Number',
        DATE   => 'Date',
        DSP_TO => 'Tech',
        NAME   => 'Company Name'
    );

    my %display_labels = (
        detail  => 'Detailed View',
        summary => 'Summary View',
    );

    # Fill out search form options:
    $template->param(
        title => $title,
        localtime => $localtime,
        startform => $q->start_form(
            -method => 'POST',
            -action => 'report',
        ),
        endform => $q->end_form(),
        customer_popup => $q->popup_menu(
            -name => 'cust_no',
            -values => [ sort {
                 $customers->{$a} cmp $customers->{$b} } keys %$customers ],
            -labels  => \%$customers,
            -default => 'all',
        ),
        cust_sub => $q->checkbox(
            -name => 'include_subs',
            -value => 1,
            -selected => 0,
            -label => '  Include associated accounts',
        ),
        no_notes => $q->checkbox(
            -name => 'no_notes',
            -value => 1,
            -selected => 0,
            -label => '  Show only calls with no notes',
        ),
        call_status => $q->popup_menu(
            -name => 'call_status',
            -values => [ 'open', 'closed' ],
            -labels => \%call_labels,
            -default => 'open',
        ),
        number_of_days => $q->popup_menu(
            -name => 'num_days',
            -values => [ 0 .. 1460 ],
            -default => '0',
        ),
        technames_popup => $q->popup_menu(
            -name => 'tech_id',
            -values => [ sort { $technames->{$a} cmp $technames->{$b} }
                 keys %$technames ],
            -labels  => \%$technames,
            -default => 'all',
        ),
        sort_popup => $q->popup_menu(
            -name => 'sort_id',
            -values => [ sort { $sort_labels{$a} cmp $sort_labels{$b} }
                 keys %sort_labels ],
            -labels => \%sort_labels,
            -default => 'NUMBER',
        ),
        display_popup => $q->popup_menu(
            -name => 'display_id',
            -values => [ sort { $display_labels{$a} cmp $display_labels{$b} }
                 keys %display_labels ],
            -labels  => \%display_labels,
            -default => 'detail',
        )
    );
    return $template->output;
}

sub call_report
{
    my $self = shift;
    my $q = $self->query();
    
    my $status =  
    $self->param('call_status') 
        || $q->param('call_status') 
        || 'open';

    if ($status eq "open") {
      show_open_calls($self);
    }
    elsif ($status eq "closed") {
      show_closed_calls($self);   
    }   else {
      call_search_form($self);
    }
}

sub show_closed_calls
{
    my $self = shift;
    my $q = $self->query();
    my $calls = get_call_headers($self); # @calls array
    my $notes = get_call_notes();        # @notes array
    my $callfixes  = get_call_fixes();
    my ( $count, $count_24, $count_48 ); 
    
    # THIS IS NOT IDEAL but sill faster than parsing the .DAT
    tie my %maintenance_recs, 'Tie::File::AsHash',
        $DBPATH . 'maintenance_recs.dbh', split => ':'
            or die "Problem tying %maintenance_recs: $!";
    
    # Unpack the query object's variable, set defaults if $q is not available
    my $report_id = $self->param('report_id') || 'all';
    
    my $sort_id =
         $self->param('sort_id')
            || $q->param('sort_id')
            || 'NUMBER';
        
    my $display_id =
        $self->param('display_id')
            || $q->param('display_id')
            || 'detail';

    my @output;  
    foreach my $c (sort {$a->{$sort_id} cmp $b->{$sort_id}} @$calls) {         
        my $lastnote  = undef;
        my $notecount = undef;
        my @callnotes;
        foreach my $n (@$notes) {        
            next if ( $n->{NUMBER} != $c->{NUMBER} );
            my %row = (
                tech => 
                    $technames->{ $n->{TECH} },
                notedate =>
                    $dto->parse_datetime($n->{NOTEDATE} - 36161 )->mdy("/"),
                notetime =>
                    convert_clarion_time_to_ampm( $n->{NOTETIME} ),
                comments =>
                    ucfirst( join( ' ', split( ' ', $n->{COMMENTS} )))
            );
            $notecount++;
            push @callnotes, \%row;           
            # Keep track of when the lastnote was entered.
            if ( $today_clarion - $n->{NOTEDATE} == 0 ) {
                 $lastnote = "today";     
            }
            else {
                 $lastnote = ( $today_clarion - $n->{NOTEDATE} );
            }
            
            shift( @$notes ); # feeble attempt at a performance enhancments
        }
        
        # --------------------------------------------------------------------#
        #           Determine Support Status and Expiration Date              #
        #---------------------------------------------------------------------#
        
        # Get the exp. date & support code from the maintenance table hash.
        my $key = join("-", $c->{OWNER}, $c->{OWNER_SUB});
        my ($supstatus, $expired, $exp_date, $supcode);
        if ( $maintenance_recs{$key} ) {
            ( $supcode, $exp_date ) = split( /-/, $maintenance_recs{$key}) ;
            my ( $month, $day, $year ) = split( /\//, $exp_date );
            my $target = [ $year, $month, $day, 0, 0, 0 ];
        
            # Get the support status from the maint table hash.
            $supstatus = $supcodes->{$supcode} || "Not Found";
        
            # Bool variable to determine css class of Expires label.
            $expired = undef;
            my $delta = normalize_delta_YMDHMS( $today, $target );
            if ( $delta->[0] < 0 ) {
                $expired = 1;
            }
        }
        else {
            $expired = undef;
            $exp_date = "Not Found";
            $supstatus = "Not Found";
        }
        
        # Bool variable to determine css class of Support Status label.
        my $nosupport = undef;
        if ( $supstatus eq "No Support" ) {
            $nosupport = 1;
        }
        
        # Increment the counts per business rules.
        $count ++;
        
        # Lookup the site name in the customer table
        my $siteid = join(":", $c->{OWNER}, $c->{OWNER_SUB});
        
        
        my $auth_by = $c->{AUTHORIZEDBY} || "Not Found";
        # Remove any bad character data from the contact field
           $auth_by =~ s/\W+|[0-9]+//g;
        #  $auth_by =~ s/\s//;

        # Get problem resolution for this call.
        my $resolution = $callfixes->{$c->{NUMBER}} || "Not Found";

        # This builds the row elements that will be sent to the template.
        # Notes are nested in their own array for each call.
        my %row = (
            count       => $count,
            number      => $c->{NUMBER},
            name        => $customers->{$siteid} || "Not Found",
            contact     => $c->{CONTACT} || "Not Found",
            owner       => $c->{OWNER},
            owner_sub   => $c->{OWNER_SUB},
            nosupport   => $nosupport || 0,
            expired     => $expired || 0,
            exp_date    => $exp_date,
            supstatus   => $supstatus || "Not Found",
            fin_date    => $dto->parse_datetime(
                             $c->{FIN_DATE} - 36161 )->mdy("/")
                              || 'Invalid Date',
            fin_time    => convert_clarion_time_to_ampm($c->{FIN_TIME}),
            tech_id     => $c->{TECH},
            dsp_by      => $technames->{$c->{DSP_BY}},
            auth_by     => ucfirst($auth_by),
            dsp_to      => $technames->{$c->{TECH}} || "*unassigned*",
            dsp_date    => $dto->parse_datetime(
                             $c->{DSP_DATE} - 36161 )->mdy("/"),
            dsp_time    => convert_clarion_time_to_ampm( $c->{DSP_TIME}),
            problem     => ucfirst(join(' ', split(' ', $c->{COMMENTS}))),
            resolution  => ucfirst(join(' ', split(' ', $resolution))),
            daysopen    => $today_clarion - $c->{DSP_DATE},
            notecount   => $notecount,
            notes       => \@callnotes,
        );
        push @output, \%row;
    }
    untie %maintenance_recs;
    
    # Decide which template we should use (summary, detail, printer, excel):
    
    my $template;
    if ( $display_id eq "detail" ) {
        $template = $self->load_tmpl(
            'call/closed_calls_detail.tmpl',
            global_vars       => 1,
            loop_context_vars => 1,
            die_on_bad_params => 0
        );
        $template->param(
            call_loop       => \@output,
            localtime       => $localtime,
            title           => "DCRS Closed Call Search Results",
            total_calls     => $count,
        );
        return $template->output;
    }
    if ( $display_id eq "summary" ) {
        $template = $self->load_tmpl(
            'call/closed_calls_summary.tmpl',
            global_vars       => 1,
            loop_context_vars => 1,
            die_on_bad_params => 0
        );
        $template->param(
            call_loop       => \@output,
            localtime       => $localtime, 
            title           => "DCRS Closed Call Summary Results",
            total_calls     => $count,
        );
        return $template->output;
    }     
}

sub show_open_calls
{
    my $self = shift;
    my $q = $self->query();
    my $calls = get_call_headers($self);  # @calls array
    my $notes = get_call_notes();         # @notes array 
    my ($count, $count_24, $count_48); 
    
    # THIS IS NOT IDEAL but sill faster than parsing the .DAT
    tie my %maintenance_recs, 'Tie::File::AsHash',
        $DBPATH . 'maintenance_recs.dbh', split => ':'
            or die "Problem tying %maintenance_recs: $!";
            
    # Unpack the query object's variable, set defaults if $q is not available
    my $report_id =
        $self->param('report_id')
            || 'all';
    
    my $sort_id =
         $self->param('sort_id')
            || $q->param('sort_id')
            || 'NUMBER';
        
    my $display_id =
        $self->param('display_id')
            || $q->param('display_id')
            || 'detail';
    
    my @output; 
    foreach my $c (sort { $a->{$sort_id} cmp $b->{$sort_id} } @$calls ) { 
        my $lastnote  = undef;
        my $notecount = undef;
        my @callnotes;
        foreach my $n (@$notes) {        
            next if ( $n->{NUMBER} != $c->{NUMBER} );
            my %row = (
                tech => $technames->{ $n->{TECH} },
                notedate => $dto->parse_datetime($n->{NOTEDATE} - 36161 )->mdy("/"),
                notetime => convert_clarion_time_to_ampm( $n->{NOTETIME} ),
                comments => ucfirst( join( ' ', split( ' ', $n->{COMMENTS} )))
            );
            $notecount++;
            push @callnotes, \%row;
            # Keep track of when the lastnote was entered.
            if ( $today_clarion - $n->{NOTEDATE} == 0 ) {
                 $lastnote = "today";     
            } 
            else {
                 $lastnote = ($today_clarion - $n->{NOTEDATE});
            }
        }    
        if ($self->param('report_id') eq "nonotes") {
          next unless (!$lastnote);
        }
        if ($self->param('report_id') eq "nonotes24") {
            next unless ($today_clarion - $c->{DATE} >= 1);
        }

        # Get the exp. date & support code from the maintenance table hash.
        my $key = join("-", $c->{CUST_NO}, $c->{CUST_SUB});
        my ($supstatus, $expired, $exp_date, $supcode);
        if ( $maintenance_recs{$key} ) {
            ( $supcode, $exp_date ) = split( /-/, $maintenance_recs{$key}) ;
            my ( $month, $day, $year ) = split( /\//, $exp_date );
            my $target = [ $year, $month, $day, 0, 0, 0 ];
        
            # Get the support status from the maint table hash.
            $supstatus = $supcodes->{$supcode} || "Not Found";
        
            # Bool variable to determine css class of Expires label.
            $expired = undef;
            my $delta = normalize_delta_YMDHMS( $today, $target );
            if ( $delta->[0] < 0 ) {
                $expired = 1;
            }
        }
        else {
            $expired = undef;
            $exp_date = "Not Found";
            $supstatus = "Not Found";
        }   
        # Bool variable to determine css class of Support Status label.
        my $nosupport = undef;
        if ( $supstatus eq "No Support" ) {
            $nosupport = 1;
        }
                
        # Increment the counts per business rules.
        if ($today_clarion - $c->{DATE} >= 2) {
            $count_48 ++;
        }
        if ($today_clarion - $c->{DATE} >= 1) {
            $count_24++;
        }
      
        $count++;
        
        # This builds the row elements of the array
        # we will push together for the template.
        #
        # One row for each call.
        # Notes are nested in their own array for each call.
        
        my %row = (
            count     => $count,
            nosupport => $nosupport || 0,
            exp_date  => $exp_date,
            expired   => $expired || 0,
            supstatus => $supstatus,
            number    => $c->{NUMBER},
            name      => $c->{NAME},
            contact   => $c->{CONTACT},
            cust_no   => $c->{CUST_NO},
            cust_sub  => $c->{CUST_SUB},
            dsp_to    => $technames->{ $c->{DSP_TO} } || "unassigned",
            date      => $dto->parse_datetime($c->{DATE} - 36161)->mdy("/"),
            time      => convert_clarion_time_to_ampm($c->{TIME}),
            comment   => ucfirst(join(' ', split(' ', $c->{COMMENT}))),
            lastnote  => $lastnote || "No Notes",
            daysopen  => $today_clarion - $c->{DATE},
            notecount => $notecount,
            notes     => \@callnotes,
        );
        push @output, \%row;
    } 
    untie %maintenance_recs;
    
    # Decide which template we should use:   
    my $template;
    if ( $display_id eq "detail" ) {
        $template = $self->load_tmpl(
            'call/open_calls_detail.tmpl',
            global_vars       => 1,
            loop_context_vars => 1,
            die_on_bad_params => 0
        );
        $template->param(
            call_loop       => \@output,
            localtime       => $localtime,
            title           => "DCRS Open Call Search Results",
            total_calls     => $count,
            total_calls_48  => $count_48,
            total_calls_24  => $count_24
        );
        return $template->output;
    }
    if ( $display_id eq "summary" ) {
        $template = $self->load_tmpl(
            'call/open_calls_summary.tmpl',
            global_vars       => 1,
            loop_context_vars => 1,
            die_on_bad_params => 0
        );
        $template->param(
            call_loop       => \@output,
            localtime       => $localtime, 
            title           => "DCRS Open Call Summary Search Results",
            total_calls     => $count,
            total_calls_48  => $count_48,
            total_calls_24  => $count_24
        );
        
        return $template->output;
    }       
}

sub get_call_headers 
{
    # Return a list of calls based on the criteria sent from the
    # user, or else use some sensible defaults.
    my $self = shift;
    my $q = $self->query(); 
    
    # Unpack the query object's params, set defaults if $q is not available
    my $siteid       = $q->param('cust_no')      || 'all:all';     
    my @cust         = split(":", $siteid );
    my $cust_no      = $cust[0]                  || 'all';
    my $cust_sub     = $cust[1]                  || 'all';
    my $tech_id      = $q->param('tech_id')      || 'all';
    my $num_days     = $q->param('num_days')     || 0;
    my $include_subs = $q->param('include_subs') ;
    
    my $status  = $self->param('call_status')   # URL param takes precedence
                    || $q->param('call_status') # Otherwise, use query object
                    || 'open';                  # Default if nothing is passed in
            
    # Generate headers for OPEN calls:
    if ($status eq "open") {
        my @calls;
        my $dbh = new Clarion $DBPATH. 'opncall.dat'
            or die("Cannot open database file: $!");
        for (1 .. $dbh->last_record) {
            my $r = $dbh->get_record_hash($_);
            next if $r->{_DELETED};
            # Use our query results to filter the output
            next if ($r->{STATUS}  ne 'O');
            next if ($tech_id  ne 'all') and ($r->{DSP_TO} != $tech_id);
            next if ($cust_no  ne 'all') and ($r->{CUST_NO} != $cust_no);
            next if ($include_subs != 1) and ($r->{CUST_SUB} != $cust_sub);       
            push @calls, $r;
        }
        $dbh->close();
        
        return \@calls;
    }  
    # Generate headers for CLOSED calls:
    if ($status eq "closed") {
        my @calls;
        my $dbh = new Clarion $DBPATH. 'callhist.dat';
        for (1 .. $dbh->last_record) {
            my $r = $dbh->get_record_hash($_);
            next if $r->{_DELETED};
            next if ($r->{FIN_DATE} > $today_clarion);
            next if ($r->{FIN_DATE} < ($today_clarion - $num_days) );
            next if ($tech_id  ne 'all') and ($r->{TECH}  != $tech_id);
            next if ($cust_no  ne 'all') and ($r->{OWNER} != $cust_no);
            next if ($include_subs != 1) and ($r->{OWNER_SUB} != $cust_sub); 
        push @calls, $r;
      }
      $dbh->close();
      
      return \@calls;
    }
}

sub get_call_notes
{
    my @notes;
    my $dbh = new Clarion $DBPATH. 'callnote.dat';
    for (1 .. $dbh->last_record) {
        my $r = $dbh->get_record_hash($_);
        next if $r->{_DELETED};
        push @notes, $r;
    }
    $dbh->close();
    
    return \@notes;
}

sub get_call_fixes
{
    my %sH;
    my $dbh = new Clarion $DBPATH.'CALLFIX.DAT'; 
    for (1 .. $dbh->last_record) {
        my $r=$dbh->get_record_hash($_);
        next if $r->{_DELETED};     
	    $sH{$r->{NUMBER}} = $r->{RESOLUTION};
    }
    $dbh->close();
    
    return \%sH;
}

sub normalize_delta_YMDHMS
{
    my($date1,$date2) = @_;
    my(@delta);
    @delta = Delta_YMDHMS(@$date1,@$date2);
    while ($delta[1] < 0 or
        $delta[2] < 0 or
        $delta[3] < 0 or
        $delta[4] < 0 or
        $delta[5] < 0)
    {
        if ($delta[1] < 0) {
             $delta[0]--; $delta[1] += 12;
        }
        if ($delta[2] < 0) {
              $delta[1]--;
              @delta[2..5] = (0,0,0,0);
              @delta[2..5] = Delta_DHMS(Add_Delta_YMDHMS(@$date1,@delta),@$date2);
        }
        if ($delta[3] < 0) {
             $delta[2]--; $delta[3] += 24;
        }
        if ($delta[4] < 0) {
             $delta[3]--; $delta[4] += 60;             
        }
        if ($delta[5] < 0) {
             $delta[4]--; $delta[5] += 60;
        }
    }
    return \@delta;
}
  
sub get_tech_names 
{
    # Build a hash of all techs using their assigned technumber as the key
    my %tech_names;
    my $dbh = new Clarion $DBPATH. 'svtech.dat';
    for (1 .. $dbh->last_record) {
        my @r = $dbh->get_record($_, 'NUMBER', 'NAME');
        next if $r[0];
        next if ($r[1] > 77);
        $tech_names{ $r[1] } = $r[2];
    }
    $dbh->close(); 
    $tech_names{0} = "unassigned"; # Add a value for unassigned calls
    $tech_names{all} = '-Include All Technicians-';# Add a default search value
    
    return \%tech_names;
}

sub get_customer_list
{
    my %customer_list;
    my $dbh = new Clarion $DBPATH. 'custmr.dat';
    for (1 .. $dbh->last_record) {
        my @r = $dbh->get_record($_, 'NUMBER', 'SUB', 'NAME');
        next if $r[0];
        # Use account number and sub for key
        $customer_list{join(":", $r[1], $r[2])} = $r[3];
    }
    $customer_list{'all:all'} = '- Search All Accounts -';
    $dbh->close();
    
    return \%customer_list;
}

sub get_support_codes
{
    # These are lables for our customer support status codes.
    my %supcodes = (
        O  => 'On Site',
        D  => 'Depot',
        N  => 'No Support',
        P  => 'SW/Helpdesk',
        R  => 'Rental',
        L  => 'Loaner',
        MA => 'Major Account',
        WK => 'Weekday',
        WW => 'Warranty Weekday',
        WD => 'Warranty Depot',
        WO => 'Warranty Onsite'
    );
    return \%supcodes;
}

sub convert_date_to_clarion 
{
    # Takes the mdy values from localtime
    # and convert to Clarion long format date.
    my ($dt, $f);
    my @f = (localtime)[3 .. 5];    # grabs day/month/year values
    $dt = DateTime->new(
            year => $f[2] + 1900, month => $f[1] + 1, day => $f[0]);
            
    return (DateTime::Format::Excel->format_datetime($dt) + 36161);
}

sub convert_clarion_time_to_ampm
{
    # Takes time value, serialized by number of seconds in 24 hour period.
    # Converts the serial time to human readable time.
    my $t = shift;
    my ($hour, $minute, $rtime, $am_pm, $open_time);
    $rtime  = ($t - 1) / 100;
    $hour   = $rtime / 3600;
    $minute = $rtime % 3600 / 60;
    $hour   = strftime(localtime($hour));
    $minute = strftime(localtime($minute));
    $am_pm  = 'am';
    $hour >= 12  and $am_pm  = 'pm';          # 12-23 are afternoon
    $hour > 12   and $hour   = $hour - 12;    # 13-23 ==> 1-11 (PM)
    $hour == 0   and $hour   = 12;            # convert first hour
    $minute < 10 and $minute = "0$minute";    # "5" ==> "05", etc.

    return "$hour:$minute$am_pm";
}
1;